package faceattendsystem.daoImpl;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import faceattendsystem.dao.IDaytimeDao;
import faceattendsystem.entity.Daytime;
import faceattendsystem.entity.Mechanism;
import faceattendsystem.entity.PageBean;
import faceattendsystem.util.JDBCTemplate;
import faceattendsystem.util.JDBCTemplate.PreparedStatementSetter;
import faceattendsystem.util.JDBCTemplate.ResultSetCallBack;


public class DaytimeDaoImpl implements IDaytimeDao {

	@Override
	public int add(Daytime daytime) {
		String sql = "insert into daytime values(?,?,?,?,?,?)";

		int result = JDBCTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setDate(1, daytime.getDaybdate());
				pstmt.setDate(2, daytime.getDayedate());
				pstmt.setString(3, daytime.getDaytype());
				pstmt.setString(4, daytime.getMechanism().getMechid());
				pstmt.setInt(5, Integer.parseInt(daytime.getBegintime()));
				pstmt.setInt(6, Integer.parseInt(daytime.getEndtime()));
			}
		});

		return result;
	}

	@Override
	public int update(Daytime daytime) {
		String sql = "update daytime set daytype=?,dayedate=?,begintime=?,endtime=? where daybdate=?";

		int result = JDBCTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, daytime.getDaytype());
				pstmt.setDate(2, daytime.getDayedate());
				pstmt.setInt(3, Integer.parseInt(daytime.getBegintime()));
				pstmt.setInt(4, Integer.parseInt(daytime.getEndtime()));
				pstmt.setDate(5, daytime.getDaybdate());
			}
		});

		return result;
	}

	@Override
	public PageBean<Daytime> queryByMechid(String mechid, int pc, int ps) {
		PageBean<Daytime> pb = new PageBean<Daytime>();
		pb.setPc(pc);
		pb.setPs(ps);

		String sql = "select count(*) from daytime where mechid=?";

		Number num = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechid);
			}
		}, new ResultSetCallBack<Number>() {
			@Override
			public Number processRs(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		});

		int tr = 1;
		if (num != null) {
			tr = num.intValue();
		}
		pb.setTr(tr);

		sql = "select * from daytime where mechid=? order by daybdate desc,dayedate desc";

		List<Daytime> daytimeList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechid);
			}
		}, new ResultSetCallBack<Daytime>() {
			@Override
			public Daytime processRs(ResultSet rs) throws SQLException {
				Daytime daytime = new Daytime();
				daytime.setDaybdate(rs.getDate(1));
				daytime.setDayedate(rs.getDate(2));
				daytime.setDaytype(rs.getString(3));
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(4));
				daytime.setMechanism(mechanism);
				daytime.setBegintime(String.valueOf(rs.getInt(5)));
				daytime.setEndtime(String.valueOf(rs.getInt(6)));
				return daytime;
			}
		});

		pb.setBeanList(daytimeList);
		return pb;
	}

	@Override
	public PageBean<Daytime> query(Daytime daytime, int pc, int ps) {
		PageBean<Daytime> pb = new PageBean<Daytime>();
		pb.setPc(pc);
		pb.setPs(ps);

		String mechid = daytime.getMechanism().getMechid();
		Date daybdate = daytime.getDaybdate();
		
		String sql = "select count(*) from daytime where mechid=? and daybdate<=? and dayedate>=?";

		Number num = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechid);
				pstmt.setDate(2, daybdate);
				pstmt.setDate(3, daybdate);
			}
		}, new ResultSetCallBack<Number>() {
			@Override
			public Number processRs(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		});

		int tr = 1;
		if (num != null) {
			tr = num.intValue();
		}
		pb.setTr(tr);

		sql = "select * from daytime where mechid=? and daybdate<=? and dayedate>=? order by daybdate desc,dayedate desc";

		List<Daytime> daytimeList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechid);
				pstmt.setDate(2, daybdate);
				pstmt.setDate(3, daybdate);
			}
		}, new ResultSetCallBack<Daytime>() {
			@Override
			public Daytime processRs(ResultSet rs) throws SQLException {
				Daytime daytime = new Daytime();
				daytime.setDaybdate(rs.getDate(1));
				daytime.setDayedate(rs.getDate(2));
				daytime.setDaytype(rs.getString(3));
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(4));
				daytime.setMechanism(mechanism);
				daytime.setBegintime(String.valueOf(rs.getInt(5)));
				daytime.setEndtime(String.valueOf(rs.getInt(6)));
				return daytime;
			}
		});

		pb.setBeanList(daytimeList);
		return pb;
	}

	@Override
	public int isRepeatDate(Daytime daytime) {
		String sql = "select count(*) from daytime where mechid=? and daytype=? and ?>=daybdate and ?<=dayedate";
		
		Number num = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, daytime.getMechanism().getMechid());
				pstmt.setString(2, daytime.getDaytype());
				pstmt.setDate(3, daytime.getDaybdate());
				pstmt.setDate(4, daytime.getDaybdate());
			}
		}, new ResultSetCallBack<Number>() {
			@Override
			public Number processRs(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		});

		//System.out.println("num="+num);
		
		return num != null ? num.intValue() : 0;
	}

	@Override
	public Daytime queryByMechid(String mechid, Date nowdate) {
		String sql = "select * from daytime where mechid=? and ?>=daybdate and ?<=dayedate";
		
		Daytime daytime = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechid);
				pstmt.setDate(2, nowdate);
				pstmt.setDate(3, nowdate);
			}
		}, new ResultSetCallBack<Daytime>() {
			@Override
			public Daytime processRs(ResultSet rs) throws SQLException {
				Daytime daytime = new Daytime();
				daytime.setDaybdate(rs.getDate(1));
				daytime.setDayedate(rs.getDate(2));
				daytime.setDaytype(rs.getString(3));
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(4));
				daytime.setMechanism(mechanism);
				daytime.setBegintime(String.valueOf(rs.getInt(5)));
				daytime.setEndtime(String.valueOf(rs.getInt(6)));
				return daytime;
			}
		});
		
		return daytime;
	}
}
